package dao;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import entity.Dam;
import entity.User;
import utils.JDBCUtils;
public class DamDAO {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;


    // 添加用户的操作
    public boolean insert(Dam dam) {
        try {
            // 1.获得数据的连接
            conn = JDBCUtils.getConnection();//注册驱动程序，定义jdbcurl,获取链接对象
            //定义sql
            String sql = "INSERT INTO dam(name,river,capacity,completeDate) VALUES(?,?,?,?)" ;
            // 2.获得PreparedStatement对象
            pstmt = conn.prepareStatement(sql);
            // 发送SQL语句

            //3.替换占位符

            pstmt.setString(1, dam.getName());
            pstmt.setString(2, dam.getRiver());
            pstmt.setInt(3,dam.getCapacity());
            pstmt.setDate(4,new Date(dam.getCompleteDate().getTime()));

            int num = pstmt.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return false;
    }
    // 查询所有的User对象
    public ArrayList<Dam> findAll() {
        ArrayList<Dam> list = new ArrayList<Dam>();
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 发送SQL语句
            String sql = "SELECT * FROM dam";
            // 获得Statement对象
            pstmt = conn.prepareStatement(sql);
            //因为无占位符，无需替换
            rs = pstmt.executeQuery();
            // 处理结果集
            while (rs.next()) {
               Dam dam=new Dam();
                dam.setId(rs.getInt("id"));
                dam.setName(rs.getString("name"));
                dam.setCapacity(rs.getInt("capacity"));
                SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
                java.util.Date date;
                date=  simpleDateFormat.parse(rs.getString("completeDate"));
                dam.setCompleteDate(date);
                dam.setRiver(rs.getString("river"));

                list.add(dam);
            }
//            for (int i = 0; i < list.size(); i++) {
//                Dam dam=list.get(i);
//                System.out.println(dam.getId()+" "+dam.getName()+" "+dam.getRiver()+" "+dam.getCapacity()+dam.getCompleteDate());
//            }
            //将封装好的数据作为返回值
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return null;
    }
    // 根据id查找指定的user
    public Dam findById(int id) {
       Dam dam=null;
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 发送SQL语句
            String sql = "SELECT * FROM dam WHERE id=?" ;
            // 获得preparedStatement对象
            pstmt = conn.prepareStatement(sql);
            //替换占位符
            pstmt.setInt(1,id);
            //执行sql
            rs = pstmt.executeQuery();
            // 处理结果集
            if (rs.next()) {
               dam=new Dam();
                dam.setId(rs.getInt("id"));
                dam.setName(rs.getString("name"));
                dam.setRiver(rs.getString("river"));
                dam.setCompleteDate(rs.getDate("completeDate"));
                dam.setCapacity(rs.getInt("capacity"));
            }
            return dam;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return null;
    }

    // 根据id查找指定的user
    public Dam findByName(String name) {
        Dam dam=null;
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 发送SQL语句
            String sql = "SELECT * FROM dam WHERE name=?" ;
            //String sql = "SELECT * FROM user WHERE name='"+name+"'";//name="abc or 1=1 or name='1"
            // 获得Statement对象
            pstmt = conn.prepareStatement(sql);
            // 替换占位符
            pstmt.setString(1,name);
            //执行sql
            rs = pstmt.executeQuery();
            // 处理结果集
            if (rs.next()) {
                dam=new Dam();
                dam.setId(rs.getInt("id"));
                dam.setName(rs.getString("name"));
                dam.setRiver(rs.getString("river"));
                dam.setCompleteDate(rs.getDate("completeDate"));
                dam.setCapacity(rs.getInt("capacity"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return dam;
    }

    // 删除用户
    public boolean delete(int id) {
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 发送SQL语句
            String sql = "DELETE FROM dam WHERE id=?";
            // 获得Statement对象
            pstmt = conn.prepareStatement(sql);
            //替换占位符
            pstmt.setInt(1,id);
            //执行sql
            int num = pstmt.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return false;
    }
    // 修改用户
    public boolean update(Dam dam) {
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 发送SQL语句
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String completeDate = sdf.format(dam.getCompleteDate());
            String sql = "UPDATE dam set name=?"
                    + ",river=?"
                    +",capacity=?"
                    + ",completeDate=?"
                    + " WHERE id=?";
            /*String sql = "UPDATE user set name='" + user.getName()
                    + "',password='" + user.getPassword() + "',email='"
                    + user.getEmail() + "',birthday='" + birthday
                    + "' WHERE id=" + user.getId();*/
            // 获得Statement对象
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,dam.getName());
            pstmt.setString(2,dam.getRiver());
            pstmt.setInt(3,dam.getCapacity());
            pstmt.setDate(4,new Date(dam.getCompleteDate().getTime()));
            pstmt.setInt(5,dam.getId());
            int num = pstmt.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }
        return false;
    }
}
